﻿using System;
using System.Configuration;
using System.Data;
using Oracle.ManagedDataAccess.Client;


namespace ThanhTu.GNCMedia.KQXS.DataAccess.DAL
{
    public class BaseDAL : IDisposable
    {
        private OracleConnection oraConn = null;
        private bool disposed = false;

        public BaseDAL() { if (oraConn == null) { oraConn = new OracleConnection(ConfigurationManager.ConnectionStrings["GNCMedia.KQXS"].ConnectionString); } }
        ~BaseDAL() { Dispose(false); }

        #region IDisposable Members

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    if (oraConn != null)
                    {
                        CloseOracleConnection();
                        oraConn.Dispose();
                        oraConn = null;
                    }
                }

                disposed = true;
            }
        }

        #endregion

        protected DataSet GetDataSet(OracleCommand oraComm)
        {
            /*oraComm.Connection = oraConn;
            OracleDataAdapter oraDataAdp = null;
            DataSet dsData = null;

            try
            {
                oraDataAdp = new OracleDataAdapter(oraComm);
                dsData = new DataSet();
                oraDataAdp.Fill(dsData);
            }
            catch (OracleException ex)
            {
                if (!ex.Message.Equals("Invalid operation on a closed object"))
                { throw ex; }

            }
            finally
            {
                //oraDataAdp.Dispose();
                //oraDataAdp = null;
            }

            return dsData;*/
            DataSet ds = null;

            try 
            {
                oraComm.Connection = oraConn;

                    using(ds=new DataSet())
                    using (OracleDataAdapter oraDataAdp = new OracleDataAdapter(oraComm))
                    {
                        oraDataAdp.Fill(ds);
                            //return ds;                        
                    }
                
            }
            catch (Exception ex)
            {
                if (!ex.Message.Equals("Invalid operation on a closed object"))
                { throw ex; }
            }

            return ds;
        }

        protected int ExecuteNoneQuery(OracleCommand oraComm)
        {
            oraComm.Connection = oraConn;
            int result = 0;

            try
            {
                OpenOracleConnection();
                result = oraComm.ExecuteNonQuery();
            }
            catch (OracleException ex) { throw ex; }
            finally { CloseOracleConnection(); }

            return result;
        }

        protected object ExecuteScalar(OracleCommand oraComm)
        {
            oraComm.Connection = oraConn;
            object result = null;

            try
            {
                OpenOracleConnection();
                result = oraComm.ExecuteScalar();
            }
            catch (OracleException ex) { throw ex; }
            finally { CloseOracleConnection(); }

            return result;
        }

        private void OpenOracleConnection() { if (oraConn.State == ConnectionState.Closed) { oraConn.Open(); } }
        private void CloseOracleConnection() { if (oraConn.State != ConnectionState.Closed) { oraConn.Close(); } }
    }
}
